package cjgl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cjgl.entity.Student;
import cjgl.entity.TbClass;
import cjgl.entity.Teacher;




public class StudentDAO {
	//��
	public Student finById(Integer id){
		Student student=null;
		String sql="select * from student t where t.id="+id;
		TbClassDAO classDAO = new TbClassDAO();
		Connection conn =  ConnectionFactory.getConnection();
		if(conn!=null) {
			Statement statement;
			try {
				statement  = conn.createStatement();
				ResultSet rs = statement.executeQuery(sql);
				if(rs.next()) {
					student= new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), classDAO.findById(rs.getInt(5)));
				}
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return student;
	}
	//list����
		public List<Student> findAll(){
			//����tbclassdao��ķ��� ��ʵ����
			List<Student> list1= new ArrayList<Student>();
			TbClassDAO classDAO = new TbClassDAO();
			String sql="select * from student";
			Connection conn = ConnectionFactory.getConnection();
			if(conn!=null) {
				Statement stmt;
				try {
					stmt = conn.createStatement();
					ResultSet rs = stmt.executeQuery(sql);
					while(rs.next()) {
						Student stu=new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), classDAO.findById(rs.getInt(5)));
						list1.add(stu);
					}
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return list1;
		}
		//ɾ��
		public void delete(Integer id) {
			String sql="delete from  student where id="+id;
			Connection conn=ConnectionFactory.getConnection();
			if(conn!=null) {
				Statement stmt;
				try {
					stmt = conn.createStatement();
					stmt.execute(sql);
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
			}
		}
		//�޸�//////////////////
		public void update(Student model) {
	      //String sql="update tbClass set name=?,manager=? where id=?";
			String sql="update student set name=?,password=?,stuNo=?,tbclassId=? where id=?";
			Connection conn=ConnectionFactory.getConnection();
			TbClassDAO classDAO = new TbClassDAO();
			if(conn!=null) {
				PreparedStatement pstmt;
				try {
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,model.getName());
					pstmt.setString(2,model.getPassword());
					pstmt.setString(3,model.getStuNo());
			        pstmt.setInt(4,model.getTbclass().getId());
			        pstmt.setInt(5, model.getId());
					pstmt.execute();
					conn.close();
				} catch (SQLException e) {				
					e.printStackTrace();
				}			
			}
		    }
		//����ywt//////////////////
		public synchronized void insert(Student student) {
			TbClassDAO classDAO = new TbClassDAO();
			String sql="insert into student values(null,?,?,?,?)";
			Connection conn= ConnectionFactory.getConnection();
			if(conn!=null) {
				PreparedStatement pastmt;
				try {
					pastmt = conn.prepareStatement(sql);
					pastmt.setString(1,student.getName());
					pastmt.setString(2,student.getPassword());
					pastmt.setString(3,student.getStuNo());
					pastmt.setInt(4,student.getTbclass().getId());  
					pastmt.execute();
					sql="select * from student t order by t.id desc";
					ResultSet rs = pastmt.executeQuery(sql);
					rs.next();
					student.setId(rs.getInt(1));
					conn.close();
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			}
		}
		public List<Student>findByTbClassId(Integer id){
			List<Student>list=new ArrayList<Student>();
			TbClassDAO classDAO=new TbClassDAO();
			String sql="select * from student t where t.tbclassId=?";
			Connection conn= ConnectionFactory.getConnection();
			if(conn!=null) {
			  
			    try { 
			    	PreparedStatement pstmt =conn.prepareStatement(sql);
					pstmt.setInt(1, id);
					ResultSet rs=pstmt.executeQuery();
			    while(rs.next()) {
			    	Student s=new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),classDAO.findById(rs.getInt(5)));
			        list.add(s);
			    }
					  conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	  
			}
			return list;
		}
		public Student stulogin(String username, String password) {
			Student student=null;
			TbClassDAO tbClassDAO=new TbClassDAO();
			String sql="select * from student t where t.stuNo=? and t.password=?";
			Connection conn= ConnectionFactory.getConnection();
			if(conn!=null) {
				PreparedStatement pstmt;
			try {
				 pstmt = conn.prepareStatement(sql);
	             pstmt.setString(1, username);
		         pstmt.setString(2, password);
		         ResultSet rs=pstmt.executeQuery();
		         if(rs.next()) {
				    student=new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),tbClassDAO.findById(rs.getInt(5)));
		         }
		         conn.close();
		         } catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
			return student;
		}
		
		public static void main(String[] args) {
			
			
			//Student stu = new Student(null, "����", "123", "0004", tbClass);
			StudentDAO stuDao = new StudentDAO();
			Student stulogin = stuDao.stulogin("0004", "123");
			System.out.println(stulogin );
			  Student stu1=stuDao.finById(1);
			  stu1.setName("gg");
			 //stu1.setName("dfaafdfasf");
			//Student stu=stuDao.finById(1);
			//stu.setName("sadfasgagadgfadgagdf");
			stuDao.update(stu1);
			
			
			
			
			List<Student> stu2=stuDao.findByTbClassId(3);
			//System.out.println(stu2);
			
		}

	
}
